"""
author : wlingforever
file name : db_upload
last modified time:2022.10.7

description:
    upload data from database

"""
import os
import pymysql
import json

# query working log to get image path
# image_path = 'runs/detect/exp14'
with open("../docs/work_log.txt", 'r', encoding="UTF-8") as f:
    image_path = f.readline()
    # print(image_path)
    image_path = image_path.strip()


def image_upload_data(im_path):
    """
    Get the value of the data，used for uploading to database

    im_path:the path o the file incoming parameter

    return :
    image_values : image data
    targets_values : image data
    """
    try:
        with open(im_path, 'r', encoding="UTF-8") as f:
            # get detected image data
            image = f.readline()
            image.strip()
            # remove extra ','
            image = image.replace(",e_f", '')
            image = json.loads(image)

            # number of targets
            target_num = len(image['target'])
            print(f'target_num:{target_num}')
            image['target_nums'] = target_num
            print(image)

            # image data
            image_values = tuple(image.values())


            targets_values = []
            # get targets data
            for list_targets in image['target']:
                targets_values.append(tuple(list_targets.values()))
            #     targets data
            # print(targets_values)  # it is a list, will to tuple

            return image_values, targets_values
    except Exception as err:
        print("image_upload_data error!")
        print(err)
        return -1


# connect

username = 'rdmanager'
ur_password = 'rdm2022.'
# username = 'rdquery'
# ur_password = 'rdquery2022'
pnw = '100.65.255.40'

coon = pymysql.connect(host='localhost', port=3306,
                       user=username, password=ur_password,
                       database='rfo_detection', charset='utf8mb4')

try:
    # print(coon)
    # 获取游标对象
    with coon.cursor() as cursor:
        # 通过对象向数据库发sql语句，获取执行结果
        # tu = ("12", "n1", "n2")

        ims_path = "D:/Projects/innovation/RFO_Detection_System/yolov5-6.2/" + image_path + "/labels"
        # find all images(files) in dictionary,get its name,need the whole path
        for root, dirs, files in os.walk(ims_path):
            for file in files:
                image_values, targets_values = image_upload_data(f'../{image_path}/labels/{file}')
                # print(image_values)

                sql_insert = "insert into tb_i_data (image_name, image_path,detect_time,target_nums) " \
                             "values (%s,%s,%s,%s) "
                sql_args = (image_values[0], image_values[1], image_values[2], image_values[4])
                # execute statement
                affected_rows_i = cursor.execute(sql_insert, sql_args)

                if affected_rows_i == 1:
                    print("添加image成功!")
                    # 提交,then target allowed to add;2022.10.8;no need!
                    # coon.commit()
                for target in targets_values:
                    sql_insert = "insert into tb_o_data (target_iname,target_pn,class,cls_num,xywh,conf) " \
                                 "values (%s,%s,%s,%s,%s,%s)"
                    sql_args = (image_values[0], target[1], target[2], target[3], target[4], target[5])
                    # execute statement
                    affected_rows_o = cursor.execute(sql_insert, sql_args)

                if affected_rows_o == 1:
                    print("添加target成功!")
            # prevent infinite loop
            break
        print("database operation complete!")

    # 提交
    coon.commit()
except pymysql.MySQLError as err:
    print("insert error!")
    print(err)
    coon.rollback()
finally:
    coon.close()
    

